# -*- coding: utf-8 -*-
import psycopg2
from psycopg2 import Error

def create_tables():
    connection = None  # Initialize to None to handle connection failure
    try:
        # Establish a connection
        connection = psycopg2.connect(
            user="postgres",
            password="ffxshlsy666",
            host="localhost",
            port="5432",
            database="transaction_lab"
        )
        
        # Create a cursor
        cursor = connection.cursor()
        
        # Create tables
        create_accounts_table = """
        CREATE TABLE IF NOT EXISTS accounts (
        account_id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        balance DECIMAL(15, 2) NOT NULL
        );
        """
        
        create_transactions_table = """
        CREATE TABLE IF NOT EXISTS transactions (
        transaction_id SERIAL PRIMARY KEY,
        from_account INTEGER REFERENCES accounts(account_id),
        to_account INTEGER REFERENCES accounts(account_id),
        amount DECIMAL(15, 2) NOT NULL,
        transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        """
        
        # Execute queries
        cursor.execute(create_accounts_table)
        cursor.execute(create_transactions_table)
        
        # Validate changes
        connection.commit()
        print("Tables created successfully")
        
    except (Exception, Error) as error:
        print("Error creating tables:", error)
    finally:
        # Safely close connection if it exists
        if connection:
            try:
                cursor.close()
            except:
                pass  # Ignore if cursor wasn't created
            connection.close()
            print("PostgreSQL connection closed")

if __name__ == "__main__":
    create_tables()